package com.cn.lyl.dao.impl;

import com.cn.lyl.dao.UserDao;
import com.cn.lyl.model.UserModel;
import com.cn.lyl.util.JDBC;
import com.cn.lyl.util.Tool;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "select username ,enable from user where username='" + userModel.getUsername() + "' and password=md5('" + userModel.getPassword() + "')";
        ResultSet resultSet = JDBC.query(sql);
        System.out.println(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
                model.setEnable(resultSet.getInt("enable"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    @Override
    public UserModel findByUsernameAndPasswordEnable(UserModel userModel) {
        String sql = "select username,enable from user where username='" + userModel.getUsername() + "' and password=md5('" + userModel.getPassword() + "')and enable=1";
        ResultSet resultSet = JDBC.query(sql);
        System.out.println(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
                model.setEnable(resultSet.getInt("enable"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }


    @Override
    public int add(UserModel userModel) {

        System.out.println(userModel.getMobile());
        String sql = "insert into user (username,password,gender,birthday,email,mobile,hobby,weight,address,create_time,update_time,enable,remark) VALUES" +
                "('" + userModel.getUsername() + "',md5('" + userModel.getPassword() + "'),'" +
                Tool.stringIsNotNull(userModel.getGender()) + "','" + Tool.stringIsNotNull(userModel.getBirthday()) + "','" +
                Tool.stringIsNotNull(userModel.getEmail()) + "','" + Tool.stringIsNotNull(userModel.getMobile()) + "','" + userModel.getHobby() + "'," +
                Tool.floatIfNotNull(userModel.getWeight()) + ",'" + Tool.stringIsNotNull(userModel.getAddress()) + "',now(),now(),1,'')";

        return JDBC.update(sql);
    }

    @Override
    public int updateInfo(UserModel userModel) {
        String sql = "update user set ";
        StringBuffer stringBuffer = new StringBuffer();
        if (!"".equals(userModel.getUsername()) && userModel.getUsername() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            if (findByUserName(userModel) != null) {
                return 2;
            }
            stringBuffer.append(" username='" + userModel.getUsername() + "'");
        }
        if (!"".equals(userModel.getGender()) && userModel.getGender() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" gender='" + userModel.getGender() + "'");
        }
        if (!"".equals(userModel.getBirthday()) && userModel.getBirthday() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" birthday ='" + userModel.getBirthday() + "'");
        }
        if (!"".equals(userModel.getEmail()) && userModel.getEmail() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" email ='" + userModel.getEmail() + "'");
        }
        if (!"".equals(userModel.getHobby()) && userModel.getHobby() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" hobby ='" + userModel.getHobby() + "'");
        }
        if (!"".equals(userModel.getWeight()) && userModel.getWeight() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" weight =" + userModel.getWeight());
        }
        if (!"".equals(userModel.getAddress()) && userModel.getAddress() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" address ='" + userModel.getAddress() + "'");
        }
        if (!"".equals(userModel.getMobile()) && userModel.getMobile() != null) {
            if (stringBuffer.length() > 0) {
                stringBuffer.append(",");
            }
            stringBuffer.append(" mobile ='" + userModel.getMobile() + "'");
        }
        if (stringBuffer.length() > 0) {

            stringBuffer.append(",update_time = now()");
        }
        if (stringBuffer.length() > 0) {

            stringBuffer.append(",");
        }
        stringBuffer.append(" remark='' where id=" + userModel.getId());
        sql = sql + stringBuffer.toString();
        return JDBC.update(sql);
    }

    @Override
    public int updatePassword(UserModel userModel) {
        String sql = "update user set password =md5('" + userModel.getPassword() + "'),update_time=now() where id =" + userModel.getId();
        return JDBC.update(sql);
    }


    @Override
    public int delete(UserModel userModel) {
        String sql = "UPDATE user SET enable=0,remark='该用户已注销,update_time=now() where id=" + userModel.getId();
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(int id) {
        String sql = "select username,password,gender,DATE_FORMAT(birthday,'%Y-%m-%d')as birthday,email,mobile,hobby,weight,address,create_time,update_time,enable,remark from user where id=" + id;
        return setList(sql).size() > 0 ? setList(sql).get(0) : null;
    }

    @Override
    public List<UserModel> findAll(UserModel userModel) {
        String sql = "select id, username,gender,DATE_FORMAT(birthday,'%Y-%m-%d')as birthday,email,mobile,hobby,weight,address,create_time,update_time,enable,remark from user where 1=1 ";
        String stringSql = commonSql(userModel);
        int pageCurr=userModel.getPage().getPageCurr();
        int pageSize=userModel.getPage().getPageSize();
        stringSql +=" order by id desc limit "+(pageCurr-1)*pageSize+","+pageSize;
        sql = sql + stringSql;
        System.out.println(sql);
        return setList(sql);
    }

    private List<UserModel> setList(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<String> colList = new ArrayList<>();
        try {
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); ++i) {
                colList.add(metaData.getColumnLabel(i));

            }
        } catch (SQLException e) {
            return null;
        }
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel model = new UserModel();
                if (colList.contains("id")) {
                    model.setId(resultSet.getInt("id"));
                }
                if (colList.contains("username")) {
                    model.setUsername(resultSet.getString("username"));
                }
                if (colList.contains("create_time")) {
                    model.setCreateTime(resultSet.getString("create_time"));
                }
                if (colList.contains("update_time")) {
                    model.setUpdateTime(resultSet.getString("update_time"));
                }
                if (colList.contains("enable")) {
                    model.setEnable(resultSet.getInt("enable"));
                }
                if (colList.contains("weight")) {
                    model.setWeight(resultSet.getFloat("weight"));
                }
                if (colList.contains("remark")) {
                    model.setRemark(resultSet.getString("remark"));
                }
                if (colList.contains("gender")) {
                    model.setGender(resultSet.getString("gender"));
                }
                if (colList.contains("birthday")) {
                    model.setBirthday(resultSet.getString("birthday"));
                }
                if (colList.contains("email")) {
                    model.setEmail(resultSet.getString("email"));
                }
                if (colList.contains("hobby")) {
                    model.setHobby(resultSet.getString("hobby"));
                }
                if (colList.contains("address")) {
                    model.setAddress(resultSet.getString("address"));
                }
                if (colList.contains("mobile")) {
                    model.setMobile(resultSet.getString("mobile"));
                }

                list.add(model);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
       /* if (list.size() == 0) {
            list.add(null);
        }*/
        return list;

    }

    private UserModel findByUserName(UserModel userModel) {
        String sql = "select username from user where username = '" + userModel.getUsername() + "' and " +
                "username != (select username from user where id =" + userModel.getId() + ")";

        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;


    }

    @Override
    public UserModel selectInfoById(int id) {

        String sql = "select username ,mobile,DATE_FORMAT(birthday,'%Y-%m-%d')as birthday,gender,hobby from user where id =" + id;
        return setList(sql).get(0);
    }

    @Override
    public int deleteUser(UserModel userModel) {
        String sql = "delete from user where id =" + userModel.getId();
        System.out.println(sql);
        return JDBC.update(sql);
    }

    @Override
    public int updateEnable(UserModel userModel) {

        String sql = "update user set enable = ABS(1-enable) where id=" + userModel.getId();
        System.out.println(sql);
        return JDBC.update(sql);
    }

    @Override
    public int countFindAll(UserModel userModel) {

        String sql="select count(id) count from user where 1=1 ";
        String stringSql = commonSql(userModel);
        sql = sql + stringSql;
        ResultSet query = JDBC.query(sql);
        int size = 0;
        try {
            while (query.next()) {
                size = query.getInt("count");
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return size;
    }

    private String commonSql(UserModel userModel){
        StringBuffer stringBuffer = new StringBuffer();
        if (userModel.getUsername() != null && !"".equals(userModel.getUsername())) {
            stringBuffer.append(" and username like'%" + userModel.getUsername() + "%'");
        }
        if (userModel.getMobile() != null && !"".equals(userModel.getMobile())) {
            stringBuffer.append(" and mobile like '%" + userModel.getMobile() + "%'");
        }
        if ((userModel.getStart() != null && !"".equals(userModel.getStart())) && (userModel.getEnd() != null && !"".equals(userModel.getEnd()))) {
            stringBuffer.append(" and birthday between'" + userModel.getStart() + "' and '" + userModel.getEnd() + "'");
        }
        if ((userModel.getStart() != null && !"".equals(userModel.getStart())) && (userModel.getEnd() == null || "".equals(userModel.getEnd()))) {
            stringBuffer.append(" and birthday >='" + userModel.getStart() + "'");
        }
        if ((userModel.getStart() == null || "".equals(userModel.getStart())) && (userModel.getEnd() != null && !"".equals(userModel.getEnd()))) {
            stringBuffer.append(" and birthday <='" + userModel.getEnd() + "'");
        }
        if (!"全部".equals(userModel.getGender())) {
           stringBuffer.append( " and gender='" + userModel.getGender() + "'");
        }
        if (!"".equals(userModel.getHobby()) && userModel.getHobby() != null) {
            String[] hobbies = userModel.getHobby().split(",");
            stringBuffer.append(" and (");
            for (int i = 0; i < hobbies.length; i++) {
                if (i == 0) {
                    stringBuffer.append(" find_in_set('" + hobbies[i] + "',hobby)");
                } else {
                    stringBuffer.append(" or  find_in_set('" + hobbies[i] + "',hobby)");
                }
                stringBuffer.append(") ");

            }
        }
        return stringBuffer.toString();
    }
}
